package com.cupk.carManageMaster.common.mapper;

import com.cupk.carManageMaster.common.dto.DriverDto;
import com.cupk.carManageMaster.common.dto.PersonalDto;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @Software: IntelliJ IDEA
 * @Project: car-manage-master
 * @File: PersonalMapper.java
 * @Author: 箬玉
 * @Description: 人事数据库操作
 * @Date: 2023/6/4 15:30
 */
@Repository
@Mapper
public interface PersonalMapper {

    @Select("SELECT id FROM type_car WHERE motorcycle_type = #{carType}")
    Long getCarTypeId(@Param("carType") String carType);

    @Select("SELECT job_number FROM job_user WHERE job_number LIKE #{driverJobNumber}")
    List<String> getDriverJobNumberList(@Param("driverJobNumber") String driverJobNumber);

    @Insert("INSERT INTO job_user(job_number,employee_name,entry_time,age,privilege_level,id_number,create_time,update_time,photo_url,phone_number) VALUES (#{jobNumber},#{employeeName},NOW(),#{age},#{privilegeLevel},#{idNumber},NOW(),NOW(),#{photoUrl},#{phoneNumber})")
    void insertJobUser(@Param("jobNumber") String jobNumber, @Param("employeeName") String employeeName, @Param("age") Integer age, @Param("privilegeLevel") Integer privilegeLevel, @Param("idNumber") String idNumber, @Param("photoUrl") String photoUrl, @Param("phoneNumber") String phoneNumber);

    @Select("SELECT id FROM job_user WHERE  job_number = #{jobNumber}")
    Long getJobUserId(@Param("jobNumber") String jobNumber);

    @Insert("INSERT INTO drive_user(user_id,driving_experience,type_car_id,create_time,update_time) VALUES (#{userId},#{drivingExperience},#{typeCarId},NOW(),NOW())")
    void insertDriverUser(@Param("userId") Long userId, @Param("drivingExperience") Integer drivingExperience, @Param("typeCarId") Long typeCarId);


    @Select("SELECT COUNT(*) FROM job_user WHERE id_number = #{idNumber}")
    Integer isExistIdNumber(@Param("idNumber") String idNumber);

    @Select("SELECT job_user.id,employee_name,entry_time,id_number,age,driving_experience,motorcycle_type,photo_url,job_number,phone_number FROM job_user JOIN drive_user du ON job_user.id = du.user_id JOIN type_car tc ON tc.id = du.type_car_id WHERE privilege_level = #{privilegeLevel} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driving_experience", property = "drivingExperience", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    List<DriverDto> getDriverList(@Param("privilegeLevel") Integer privilegeLevel);

    @Select("SELECT job_user.id,employee_name,entry_time,id_number,age,driving_experience,motorcycle_type,photo_url,phone_number,job_number FROM job_user JOIN drive_user du ON job_user.id = du.user_id JOIN type_car tc ON tc.id = du.type_car_id WHERE job_number = #{jobNumber} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driving_experience", property = "drivingExperience", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    DriverDto getDriver(@Param("jobNumber") String jobNumber);

    @Select("SELECT id,employee_name,entry_time,id_number,age,photo_url,job_number,phone_number FROM job_user WHERE privilege_level = #{privilegeLevel} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    List<PersonalDto> getOtherList(@Param("privilegeLevel") Integer privilegeLevel);

    @Select("SELECT id,employee_name,entry_time,id_number,age,photo_url,phone_number,job_number FROM job_user WHERE job_number = #{jobNumber} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    PersonalDto getOther(@Param("jobNumber") String jobNumber);

    @Update(("UPDATE job_user SET employee_name = #{employeeName},entry_time = #{entryTime},age = #{age},id_number = #{idNumber},phone_number = #{phoneNumber}, update_time = NOW() WHERE id = #{id}"))
    void changeUser(@Param("id") Long id, @Param("employeeName") String employeeName, @Param("entryTime") Date entryTime, @Param("age") Integer age, @Param("idNumber") String idNumber, @Param("phoneNumber") String phoneNumber);

    @Update("UPDATE drive_user SET driving_experience = #{driving_experience},type_car_id = #{typeCarId}, update_time = NOW() WHERE user_id = #{id}")
    void changeDriver(@Param("id") Long id, @Param("driving_experience") Integer driving_experience, @Param("typeCarId") Long typeCarId);

    @Update("UPDATE job_user SET is_delete = TRUE,update_time = NOW() WHERE id = #{id}")
    void deleteUser(@Param("id") Long id);

    @Update("UPDATE drive_user SET is_delete = TRUE,update_time = NOW() WHERE user_id = #{id}")
    void deleteDriver(@Param("id") Long id);

    @Select("SELECT COUNT(*) FROM job_user WHERE id = #{id} AND user_password = #{password}")
    Integer verifyPassword(@Param("id") Long id, @Param("password") String password);

    @Update("UPDATE job_user SET user_password = #{password},update_time = NOW() WHERE id = #{id}")
    void changePassword(@Param("id") Long id, @Param("password") String password);

}
